package com.dudu.util;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by dudu on 16/2/29.
 */
public class ReadExcel {

    static final Logger logger = LoggerFactory.getLogger(ReadExcel.class);

    public static final String EXCEL_2003_SUFFIX = "xls";
    public static final String EXCEL_2007_SUFFIX = "xlsx";
    public static final String POINT = ".";

    /**
     * read the Excel file
     * @return
     * @throws IOException
     */
    public static List<Object[]> readExcel(InputStream is, String name) throws IOException {
        String suffix = getSuffix(name);
        if (!StringUtils.isEmpty(name)) {
            if (EXCEL_2003_SUFFIX.equals(suffix)) {
                return readXls(is);
            } else if (EXCEL_2007_SUFFIX.equals(suffix)) {
                return readXlsx(is);
            }
        } else {
            logger.error(name + " is not excel file!");
        }
        return null;
    }

    private static String getSuffix(String path) {
        if (StringUtils.isEmpty(path)) {
            return "";
        }
        if (path.contains(POINT)) {
            return path.substring(path.lastIndexOf(POINT) + 1, path.length());
        }
        return "";
    }
    

    /**
     * Read the Excel 2010
     * @return
     * @throws IOException
     */
    private static List<Object[]> readXlsx(InputStream is) throws IOException {
//        logger.info("正在处理..." + excelFile.getName());
//        InputStream is = new FileInputStream(excelFile);
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
        List<Object[]> list = new ArrayList<>();
        // Read the Sheet
        for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
            if (xssfSheet == null) {
                continue;
            }
            // Read the Row
            for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
                XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                if (xssfRow != null) {
                    Object[] row_list = new Object[xssfRow.getLastCellNum()];
                    for(int col = 0;col<xssfRow.getLastCellNum();col++){
//                        row_list.add(getValue(xssfRow.getCell(col)));
                        XSSFCell cell = xssfRow.getCell(col);
                        if(cell == null){
                            continue;
                        }else {
                            row_list[col] = getValue(cell);
                        }
                    }

                    list.add(row_list);
                }
            }
        }
        return list;
    }

    /**
     * Read the Excel 2003-2007
     * @return
     * @throws IOException
     */
    private static List<Object[]> readXls(InputStream is) throws IOException {
//        logger.info("正在处理..." + excelFile.getName());
//        InputStream is = new FileInputStream(excelFile);
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
        List<Object[]> list = new ArrayList<>();
        // Read the Sheet
        for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
            if (hssfSheet == null) {
                continue;
            }
            // Read the Row
            for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow != null) {
                    Object[] row_list = new Object[hssfRow.getLastCellNum()];
                    for(int col = 0;col<hssfRow.getLastCellNum();col++){
//                        row_list.add(getValue(hssfRow.getCell(col)));
                        HSSFCell cell = hssfRow.getCell(col);
                        if(cell == null){
                            continue;
                        }else{
                            row_list[col] = getValue(cell);
                        }
                    }
                    list.add(row_list);
                }
            }
        }
        return list;
    }

    @SuppressWarnings("static-access")
    private static String getValue(XSSFCell xssfRow) {
        if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {
            return String.valueOf(xssfRow.getBooleanCellValue());
        } else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
            return String.valueOf(xssfRow.getNumericCellValue());
        } else {
            if("null".equals(xssfRow.getStringCellValue())){
                return null;
            }
            return String.valueOf(xssfRow.getStringCellValue());
        }
    }

    @SuppressWarnings("static-access")
    private static String getValue(HSSFCell hssfCell) {
        if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(hssfCell.getBooleanCellValue());
        } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
            return String.valueOf(hssfCell.getNumericCellValue());
        } else {
            if("null".equals(hssfCell.getStringCellValue())){
                return null;
            }
            return String.valueOf(hssfCell.getStringCellValue());
        }
    }
    

}
